package com.bringway.p2p.dao.impl;
 
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.ObjectUtils.Null;
import org.apache.log4j.Logger;
import org.apache.log4j.spi.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import com.bringway.framework.dao.P2PJdbcDao;
import com.bringway.framework.util.RequestUtils;
import com.bringway.global.util.StringUtils;
import com.bringway.p2p.dao.P2PGrtzzqDao;
import com.bringway.p2p.dao.P2PJhntzxiDao;

import net.sf.json.JSONObject;

/*
 * @author qjy
 * @className P2PDaoImpl
 * @description 计划内投资债权信息持久层dao实现类
 * @date 2016年7月26日下午11:10:18
 */
@Repository
public class P2PJhntzxiDaoImpl extends P2PJdbcDao implements P2PJhntzxiDao {
	
	Logger logger = Logger.getLogger(P2PJhntzxiDaoImpl.class);

	/*
	 * 
	 */
	@Override
	public List queryJhntzxi(JSONObject parms) {
		String userId  = parms.optString("userId");
		String TZSJ_START = parms.optString("TZSJ_START");
		String TZSJ_END =parms.optString("TZSJ_END");
		if(StringUtils.isNotEmpty(TZSJ_START)){
			TZSJ_START=TZSJ_START+" 00:00:00";
		}
		if(StringUtils.isNotEmpty(TZSJ_END)){
			TZSJ_END=TZSJ_END+" 23:59:59";
		}
		String REF_XM_ORDER = parms.optString("REF_XM_ORDER");
		String HTBH =parms.optString("HTBH");
		String TZZT =parms.optString("TZZT");
		String HKZT = parms.optString("HKZT");
		int start = parms.optInt("start");
		int limit = parms.optInt("limit");
		StringBuffer sql =new StringBuffer();
		Map<String, Object> parmsMap = new HashMap<String, Object>();
		parmsMap.put("START", start);
		parmsMap.put("LIMIT", limit);
		sql.append(" SELECT  T1.ID,T1.USERID, T1.REF_XM_ID, T1.REF_XM_ORDER, T1.TZJE, T1.TZSJ, T1.TZZT, T1.HKZT, T1.JXRQ, T1.TZSY, T1.HTBH , t3.DQ,t3.BM,t3.SS,t3.ZW,t3.ZJ,t3.XM,t3.SJHM");
		sql.append(" FROM t_zhfw_jhn_tzrxx  T1 ");
		sql.append(" LEFT JOIN t_zhfw_user t2 ON t1.userid = t2.USER_NAME");
		sql.append(" LEFT JOIN t_zhfw_oa_user t3 ON t2.TJR = t3.GYH");
		sql.append(" where 1=1 ");

		if(StringUtils.isNotEmpty(userId)){
			sql.append(" AND T1.USERID like "+"'%"+userId+"%'");
		}
		if(StringUtils.isNotEmpty(REF_XM_ORDER)){
			sql.append(" AND T1.REF_XM_ORDER like '%"+REF_XM_ORDER+"%' ");
		}
		if(StringUtils.isNotEmpty(TZSJ_START)){
			sql.append(" AND date_format(T1.TZSJ,'%Y-%m-%d %H:%i:%s') >= :TZSJ_START ");
			parmsMap.put("TZSJ_START",TZSJ_START);
		}
		if(StringUtils.isNotEmpty(TZSJ_END)){
			sql.append(" AND date_format(T1.TZSJ,'%Y-%m-%d %H:%i:%s') <= :TZSJ_END ");
			parmsMap.put("TZSJ_END",TZSJ_END);
		}
		
		if(StringUtils.isNotEmpty(HTBH)){
			sql.append(" and T1.HTBH = :HTBH ");
			parmsMap.put("HTBH", HTBH);
		}
		if(StringUtils.isNotEmpty(TZZT)){
			sql.append(" and T1.TZZT = :TZZT ");
			parmsMap.put("TZZT", TZZT);
		}
		
		if(StringUtils.isNotEmpty(HKZT)){
			sql.append(" AND T1.HKZT like '%"+HKZT+"%' ");
		}
		
		sql.append(" order by T1.TZSJ DESC ");
		
		if(0 != limit){
			sql.append(" LIMIT :START,:LIMIT");
		}
		logger.info("queryjhntzxi sql is : " + sql.toString());
		logger.info("queryjhntzxi  parmsMap is : " + parmsMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), parmsMap);
		
	}
	
	@Override
	public int getPageCount(JSONObject parms) {
		String userId  = parms.optString("userId");
		String TZSJ_START = parms.optString("TZSJ_START");
		String TZSJ_END =parms.optString("TZSJ_END");
		if(StringUtils.isNotEmpty(TZSJ_START)){
			TZSJ_START=TZSJ_START+" 00:00:00";
		}
		if(StringUtils.isNotEmpty(TZSJ_END)){
			TZSJ_END=TZSJ_END+" 23:59:59";
		}
		String REF_XM_ORDER = parms.optString("REF_XM_ORDER");
		String HTBH =parms.optString("HTBH");
		String TZZT =parms.optString("TZZT");
		String HKZT = parms.optString("HKZT");
		int start = parms.optInt("start");
		int limit = parms.optInt("limit");
		StringBuffer sql =new StringBuffer();
		Map<String, Object> parmsMap = new HashMap<String, Object>();
		parmsMap.put("START", start);
		parmsMap.put("LIMIT", limit);
		sql.append(" SELECT count(*)");
		sql.append(" FROM t_zhfw_jhn_tzrxx  ");
		sql.append(" where 1=1 ");
		if(StringUtils.isNotEmpty(userId)){
			sql.append(" AND USERID like "+"'%"+userId+"%'");
		}
		if(StringUtils.isNotEmpty(REF_XM_ORDER)){
			sql.append(" AND REF_XM_ORDER like '%"+REF_XM_ORDER+"%' ");
		}
		if(StringUtils.isNotEmpty(TZSJ_START)){
			sql.append(" AND date_format(TZSJ,'%Y-%m-%d %H:%i:%s') >= :TZSJ_START ");
			parmsMap.put("TZSJ_START",TZSJ_START);
		}
		if(StringUtils.isNotEmpty(TZSJ_END)){
			sql.append(" AND date_format(TZSJ,'%Y-%m-%d %H:%i:%s') <= :TZSJ_END ");
			parmsMap.put("TZSJ_END",TZSJ_END);
		}
		
		if(StringUtils.isNotEmpty(HTBH)){
			sql.append(" and HTBH = :HTBH ");
			parmsMap.put("HTBH", HTBH);
		}
		if(StringUtils.isNotEmpty(TZZT)){
			sql.append(" and TZZT = :TZZT ");
			parmsMap.put("TZZT", TZZT);
		}
		
		if(StringUtils.isNotEmpty(HKZT)){
			sql.append(" AND HKZT like '%"+HKZT+"%' ");
		}
		
		logger.info("getPageCount sql is : " + sql.toString());
		logger.info("getPageCount  parmsMap is : " + parmsMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), parmsMap,Integer.class);
	}


	@Override
	public String getJhntzJe(JSONObject parms) {
		String userId  = parms.optString("userId");
		String TZSJ_START = parms.optString("TZSJ_START");
		String TZSJ_END =parms.optString("TZSJ_END");
		if(StringUtils.isNotEmpty(TZSJ_START)){
			TZSJ_START=TZSJ_START+" 00:00:00";
		}
		if(StringUtils.isNotEmpty(TZSJ_END)){
			TZSJ_END=TZSJ_END+" 23:59:59";
		}
		String REF_XM_ORDER = parms.optString("REF_XM_ORDER");
		String HTBH =parms.optString("HTBH");
		String TZZT =parms.optString("TZZT");
		String HKZT = parms.optString("HKZT");
		int start = parms.optInt("start");
		int limit = parms.optInt("limit");
		StringBuffer sql =new StringBuffer();
		Map<String, Object> parmsMap = new HashMap<String, Object>();	
		parmsMap.put("START", start);
		parmsMap.put("LIMIT", limit);
		sql.append(" SELECT sum(TZJE) TZJE from t_zhfw_jhn_tzrxx ");
		sql.append(" where 1=1 ");
		if(StringUtils.isNotEmpty(userId)){
			sql.append(" AND USERID like "+"'%"+userId+"%'");
		}
		if(StringUtils.isNotEmpty(REF_XM_ORDER)){
			sql.append(" AND REF_XM_ORDER like '%"+REF_XM_ORDER+"%' ");
		}
		if(StringUtils.isNotEmpty(TZSJ_START)){
			sql.append(" AND date_format(TZSJ,'%Y-%m-%d %H:%i:%s') >= :TZSJ_START ");
			parmsMap.put("TZSJ_START",TZSJ_START);
		}
		if(StringUtils.isNotEmpty(TZSJ_END)){
			sql.append(" AND date_format(TZSJ,'%Y-%m-%d %H:%i:%s') <= :TZSJ_END ");
			parmsMap.put("TZSJ_END",TZSJ_END);
		}
		
		if(StringUtils.isNotEmpty(HTBH)){
			sql.append(" and HTBH = :HTBH ");
			parmsMap.put("HTBH", HTBH);
		}
		if(StringUtils.isNotEmpty(TZZT)){
			sql.append(" and TZZT = :TZZT ");
			parmsMap.put("TZZT", TZZT);
		}
		
		if(StringUtils.isNotEmpty(HKZT)){
			sql.append(" AND HKZT like '%"+HKZT+"%' ");
		}
		
		logger.info("queryjhntzxi sql is : " + sql.toString());
		logger.info("queryjhntzxi  parmsMap is : " + parmsMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), parmsMap, String.class);

		
			
	}
	
	
}
